import pymysql
import re
import urllib.parse
import logging

URL_FUNC_DICT = dict()


def route(url):
    def set_func(func):
        URL_FUNC_DICT[url] = func

        def call_func(*args, **kwargs):
            return func(*args, **kwargs)
        return call_func
    return set_func


@route(r'/index.html')  # index = route('/index.py')
def index(ret):
    with open('./template/index.html') as f:
        content = f.read()
    conn = pymysql.connect(host='localhost', port=3306, user='chenqx', passwd='587hh*12', db='stock_db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM info")
    cur_data = cursor.fetchall()
    cursor.close()
    conn.close()
    html_template = """
              <tr>
                <td>%d</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>
                    <input type="button" value="添加" id="toAdd" name="toAdd" systemidvaule="%s">
                </td>
                </tr>
    """
    html = ''
    for info in cur_data:
        html += html_template%(info[0],info[1],info[2],info[3],info[4],info[5],info[6],info[7],info[1])
    content = re.sub(r'\{%content%\}', html, content)
    return content


@route(r'/center.html')
def center(ret):
    with open('./template/center.html') as f:
        content = f.read()
    # info_from_mysql = 'I come from mysql,too'
    # content = re.sub(r'\{%content%\}', info_from_mysql, content)
    conn = pymysql.connect(host='localhost', port=3306, user='chenqx', passwd='587hh*12', db='stock_db')
    cursor = conn.cursor()
    cursor.execute("select i.code,i.short,i.chg,i.turnover,i.price,i.highs,j.note_info from info as i inner join focus as j on i.id = j.info_id;")
    cur_data = cursor.fetchall()
    cursor.close()
    conn.close()
    html_template = '''
    <tr>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>
                    <a type="button" class="btn btn-default btn-xs" href="/update/%s.html"> <span class="glyphicon glyphicon-star" aria-hidden="true"></span> 修改 </a>
                </td>
                <td>
                    <input type="button" value="删除" id="toDel" name="toDel" systemidvaule="%s">
                </td>
            </tr>
    '''
    html = ''
    for info in cur_data:
        html += html_template%(info[0],info[1],info[2],info[3],info[4],info[5],info[6],info[0],info[0])
    content = re.sub(r'\{%content%\}', html, content)
    return content


@route(r'/add/(\d*)\.html')
def add(ret):
    # 1 get the code about you choose ,just the number
    code = ret.group(1)

    # 2 find this code if not in the databases
    conn = pymysql.connect(host='localhost', port=3306, user='chenqx', passwd='587hh*12', db='stock_db')
    cursor = conn.cursor()
    cursor.execute("select code from info where code = %s"%code)
    # cur_data = cursor.fetchone()
    # print(cur_data)
    if not cursor.fetchone():
        cursor.close()
        conn.close()
        return 'we did not have this data'

    # 3 judge if you order it before
    cursor.execute("select * from focus as i inner join info as j on i.info_id = j.id where j.code = %s" % code)
    if cursor.fetchone():
        cursor.close()
        conn.close()
        return 'you had already add it'

    # 4 insert the data you choose
    cursor.execute("insert into focus (info_id) select id from info where code=%s;"%code)
    conn.commit()
    cursor.close()
    conn.close()
    return 'add is ok'


@route(r"/update/(\d*)\.html")
def update_show(ret):
    with open('./template/update.html') as f:
        content = f.read()
    code = ret.group(1)
    conn = pymysql.connect(host='localhost', port=3306, user='chenqx', passwd='587hh*12', db='stock_db')
    cursor = conn.cursor()
    # cursor.execute("update focus inner join info on focus.info_id = info.id set focus.note_info = %s where info.code=%s;" % (note_info,code))
    cursor.execute(
        "select focus.note_info from focus inner join info on focus.info_id=info.id where info.code=%s;" % code)
    note_info = cursor.fetchone()
    cursor.close()
    conn.close()
    content = re.sub(r'\{%code%\}', code, content)
    content = re.sub(r'\{%note_info%\}', str(note_info[0]), content)

    return content


@route(r"/update/(\d*)/(.*)\.html")
def update_note_info(ret):
    code = ret.group(1)
    note_info = ret.group(2)
    note_info = urllib.parse.unquote(note_info)

    conn = pymysql.connect(host='localhost', port=3306, user='chenqx', passwd='587hh*12', db='stock_db')
    cursor = conn.cursor()
    cursor.execute("""update focus inner join info on focus.info_id=info.id set focus.note_info="%s" where info.code=%s;""" % (note_info,code))
    conn.commit()
    cursor.close()
    conn.close()
    return "insert is ok"


@route(r"/del/(\d*)\.html")
def delete(ret):
    # 1 get the code about you choose ,just the number
    code = ret.group(1)

    # 2 find this code if not in the add
    conn = pymysql.connect(host='localhost', port=3306, user='chenqx', passwd='587hh*12', db='stock_db')
    cursor = conn.cursor()
    cursor.execute("select * from focus inner join info on info.id = focus.info_id where info.code = %s"%code)
    # cur_data = cursor.fetchone()
    # print(cur_data)
    if not cursor.fetchone():
        cursor.close()
        conn.close()
        return '%s you did not add this data'% code

    # 4 insert the data you choose
    cursor.execute("delete from focus where focus.info_id = (select id from info where code = %s);"%code)
    conn.commit()
    cursor.close()
    conn.close()
    return 'del is ok'


print(URL_FUNC_DICT)
# URL_FUNC_DICT = {
#     '/index.py':index,
#     '/center.py':center
# }


def application(env, start_response):
    status = '200 OK'
    response_header = [('Content-Type', 'text/html')]
    start_response(status, response_header)
    file_name = env['PATH_INFO']
    # func = URL_FUNC_DICT[file_name]
    # return func()
    logging.basicConfig(level=logging.INFO,
                        filename='./log.txt',
                        filemode='a',
                        format='%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s')
    logging.info('visiting %s'%file_name)
    try:
        for url, call_func in URL_FUNC_DICT.items():
            print('--------------------------'+url)
            print('--------------------------'+file_name)
            ret = re.match(url,file_name)
            if ret:
                return call_func(ret)
        else:
            logging.warning('no this page-------%s'%file_name)
            return 'no this page-------%s'%file_name
    except Exception as ret:
        return "%s"%ret